"""
将数据库中的订单信息转换为json文件，用于langchain进行后续处理
每一个json文件为一个订单
包括订单、商品、购买企业等信息
"""
from sqlalchemy import create_engine
from pandas import read_sql,DataFrame,Series
import urllib,os,json
from datetime import datetime

from tqdm import tqdm

def format_datetime(d:datetime):
   return d.strftime("%Y-%m-%d %H:%M:%S")

db_file_path=r'.\data\hysystemdb.mdb'
connection_string = (
  r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
  f"DBQ={db_file_path};"
  r"ExtendedAnsiSQL=1;"
)
connection_uri = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
engine = create_engine(connection_uri)

def orderGoodsData(order_id:int):
  query=rf"""SELECT 
  hog.[goodsnum] AS 购买数量,
  hog.[orderprice] AS 商品单价,
  hog.[amount] AS 商品合计金额,
  hg.[goodsname] AS 商品名称,
  hg.[goodsmodel] AS 商品规格,
  hg.[goodsunit] AS 商品单位,
  hg.[goodsbrand] AS 商品品牌,
  hg.[madeplace] AS 商品产地
FROM [hy_ordergoods] AS hog
LEFT JOIN [hy_goods] AS hg ON hog.[goodsid] = hg.[id]
WHERE hog.[orderid] =?
  """
  df:DataFrame = read_sql(query, engine,params=(order_id,))
  ret=[]
  for (_, row) in df.iterrows():
    ret.append(row.to_dict())
  return ret

def orderData():
  query=rf"""SELECT 
  ho.[id] AS 订单id,
  ho.[orderbh] AS 订单编号,
  ho.[orderstate] AS 订单状态,
  ho.[createtime]  AS 下单时间,
  ho.[amount] AS 订单金额,
  ho.[address] AS 订单地址,
  ho.[personname] AS 订单收件人,
  ho.[tell] AS 订单联系电话,
  ho.[note] AS 订单留言,
  hc.[companyname] AS 下单企业,
  hc.[linkmanname] AS 企业联系人,
  hc.[linktell] AS 企业联系电话,
  hc.[address] AS 企业地址
FROM [hy_order] AS ho
  LEFT JOIN [hy_company] AS hc ON ho.[customerid] =hc.[id]  """
  df:DataFrame = read_sql(query, engine)
  with tqdm(
    total=df.shape[0],
    bar_format='{l_bar}{bar}| {n_fmt}/{total_fmt} [{elapsed}<{remaining}]',
    colour='green'
  ) as pbar:
    for (_, row) in df.iterrows():
      order:dict=row.to_dict()
      goods_of_order:dict=orderGoodsData(order['订单id'])
      order['订单中的商品']=goods_of_order
      pbar.update(1)
      yield order
  return

def format_order_description(order:dict):
  goods_text:list[str]=[]
  for index,goods in enumerate(order["订单中的商品"],start=1):
    goods_text.append(f'''
商品{index}:
  商品名称:\"{goods['商品名称']}\"
  购买数量:{goods['购买数量']},
  商品单价:{goods['商品单价']},
  商品合计金额:{goods['商品合计金额']},
  商品规格:{goods['商品规格']},
  商品单位:{goods['商品单位']},
  商品品牌:{goods['商品品牌']},
  商品产地:{goods['商品产地']},
''')
  goods_list_text:str='\n'.join(goods_text)
  return f'''
<订单信息开始>
订单id:{order["订单id"]},
订单编号:{order["订单编号"]},
订单状态:{order["订单状态"]},
下单时间:{order["下单时间"]},
订单金额:{order["订单金额"]},
订单地址:{order["订单地址"]},
订单收件人:{order["订单收件人"]},
订单联系电话:{order["订单联系电话"]},
订单留言:{order["订单留言"]},
下单企业:{order["下单企业"]},
企业联系人:{order["企业联系人"]},
企业联系电话:{order["企业联系电话"]},
企业地址:{order["企业地址"]},
<订单商品信息开始>
{goods_list_text}
<订单商品信息结束>
<订单信息结束>
'''

def gen_order_file_name(order:dict):
  return f'{order["订单id"]}.txt'

data_folder=r".\data\hy"
if not os.path.exists(data_folder):
  os.makedirs(data_folder)

order_gen=orderData()
for order in order_gen:
  file_name:str=gen_order_file_name(order)
  file_path=os.path.join(data_folder,file_name)
  content=format_order_description(order)
  with open(file=file_path,mode='w',encoding='utf-8') as f:
     f.write(content)
